package com.alertdroid2;

import java.io.File;
import java.io.FileOutputStream;
import java.io.InputStream;
import java.io.OutputStream;
import java.util.ArrayList;

import android.content.Context;
import android.database.Cursor;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteOpenHelper;
import android.text.format.Time;

public class DatabaseHelper extends SQLiteOpenHelper {
	SQLiteDatabase myDb;
	String dbPath = "/data/data/com.alertdroid2/databases/";
	static String dbName = "alertdroid2";
	private final Context ctx;
	int rowCount;
	int first;

	public DatabaseHelper(Context ctx) {
		super(ctx, dbName, null, 1);
		this.ctx = ctx;
		this.createDataBase();
	}

	public void createDataBase() {
		// closeDb();
		boolean dbExist = checkDataBase();
		if (dbExist) {
			first = 1;
			// do nothing - database already exist
		} else {
			// By calling this method and empty database will be created into
			// the default system path
			// of your application so we are gonna be able to overwrite that
			// database with our database.
			getReadableDatabase();
			copyDataBase();
			first = 0;
		}
	}

	private boolean checkDataBase() {
		File dbFile = new File(dbPath + dbName);

		return dbFile.exists();
	}

	public void copyDataBase() {
		try {
			// Open your local db as the input stream
			InputStream myInput = ctx.getAssets().open(this.dbName);

			// Path to the just created empty db
			String outFileName = dbPath + dbName;

			// Open the empty db as the output stream
			OutputStream myOutput = new FileOutputStream(outFileName);

			// transfer bytes from the inputfile to the outputfile
			byte[] buffer = new byte[1024];
			int length;
			while ((length = myInput.read(buffer)) > 0) {
				myOutput.write(buffer, 0, length);
			}

			// Close the streams
			myOutput.flush();
			myOutput.close();
			myInput.close();
		} catch (Exception e) {

		}
	}

	public void executeNonQuery(String sql) {
		this.myDb.execSQL(sql);
	}

	public Cursor executeQuery(String sql) {
		Cursor cur = null;
		cur = this.myDb.rawQuery(sql, null);
		return cur;
	}

	public void openDataBase() {
		if (myDb != null)
			myDb = SQLiteDatabase.openDatabase(dbPath + dbName, null,
					SQLiteDatabase.OPEN_READWRITE);
		else
			myDb = SQLiteDatabase.openDatabase(dbPath + dbName, null,
					SQLiteDatabase.CREATE_IF_NECESSARY);
		;
		if (first == 0) {
			onUpgrade(myDb, 0, 1);
			first = 1;
		}
	}

	public synchronized void closeDb() {
		if (myDb != null)
			myDb.close();
		super.close();
	}

	public int getRowCount() {
		return this.rowCount;
	}

	public ArrayList<MemoClass> getAllMemo() {
		ArrayList<MemoClass> data = new ArrayList<MemoClass>();
		int counter = 0;
		Cursor c = myDb.rawQuery("Select a._id, a.NAMA, a.TIME, p.NAMA "
				+ "From place p, alarm a Where a._IDPLACE = p._ID", null);
		this.rowCount = c.getCount();
		if (c.moveToFirst()) {
			do {
				MemoClass memo = new MemoClass();
				memo.nama = c.getString(1);
				Time time = new Time();
				String date = c.getString(2);
				time.parse(date);
				memo.waktu = time;
				memo.place = c.getString(3);
				memo.idMemo = c.getInt(0);
				data.add(memo);
				counter++;
			} while (c.moveToNext());
		}
		return data;
	}

	public MemoClass getMemoById(int idMemo) {

		MemoClass memo = new MemoClass();
		Cursor c = myDb
				.rawQuery(
						"Select a._id, a.NAMA, a.TIME, p.NAMA, p.longitude, p.latitude "
								+ "From place p, alarm a Where a._IDPLACE = p._ID AND a._id = "
								+ idMemo, null);
		this.rowCount = c.getCount();
		if (c.moveToFirst()) {
			if (this.rowCount == 1) {
				memo.idMemo = c.getInt(0);
				memo.nama = c.getString(1);
				Time time = new Time();
				String date = c.getString(2);
				time.parse(date);
				memo.waktu = time;
				memo.place = c.getString(3);
				memo.longitude = c.getInt(4);
				memo.latitude = c.getInt(5);
			}
		}
		return memo;
	}

	public MemoClass getMemo(String name, Time _time, int idplace) {
		MemoClass memo = new MemoClass();
		String time = _time.format2445();
		Cursor c = myDb.rawQuery("Select a._id, a.NAMA "
				+ "From alarm a Where a.NAMA = '" + name
				+ "' AND a._idplace = " + idplace + " AND a.TIME = '" + time
				+ "'", null);
		this.rowCount = c.getCount();
		if (c.moveToFirst() && this.rowCount == 1) {
			memo.idMemo = c.getInt(0);
			memo.nama = c.getString(1);
		}
		return memo;
	}

	public String[] readNamePlace() {
		String[] data = null;
		int counter = 0;
		Cursor c = myDb.rawQuery("select * from place", null);
		data = new String[c.getCount()];
		if (c.moveToFirst()) {
			do {
				data[counter] = c.getString(3);
				counter++;
			} while (c.moveToNext());
		}
		return data;
	}

	public int[] readIdPlace() {
		int[] data = null;
		int counter = 0;
		Cursor c = myDb.rawQuery("select * from place", null);
		data = new int[c.getCount()];
		if (c.moveToFirst()) {
			do {
				data[counter] = c.getInt(0);
				counter++;
			} while (c.moveToNext());
		}
		return data;
	}

	public void insertMemo(String nama, Time time, int idPlace) {
		String sql = "insert into alarm (nama, _idplace, time) values ('"
				+ nama + "'," + idPlace + ",'" + time.format2445() + "')";
		myDb.execSQL(sql);
	}

	// public String[][] readConf() {
	// String[][] data = null;
	// int counter = 0;
	// Cursor c = myDb.rawQuery("SELECT * FROM config", null);
	// this.rowCount = c.getCount();
	// data = new String[c.getCount()][6];
	// if (c.moveToFirst()) {
	// do {
	// data[counter][0] = c.getString(1);
	// data[counter][1] = c.getString(2);
	// counter++;
	// } while (c.moveToNext());
	// }
	// return data;
	// }
	//
	// public String[] readStationName() {
	// String[] data = null;
	// int counter = 0;
	// Cursor c = myDb.rawQuery("SELECT name FROM station", null);
	// data = new String[c.getCount()];
	// if (c.moveToFirst()) {
	// do {
	// data[counter] = c.getString(0);
	// counter++;
	// } while (c.moveToNext());
	// }
	// return data;
	// }

	@Override
	public void onCreate(SQLiteDatabase db) {
		// TODO Auto-generated method stub
		String sql = "CREATE TABLE place (_id INTEGER PRIMARY KEY, longitude INTEGER, latitude INTEGER, nama TEXT)";
		db.execSQL(sql);
		sql = "CREATE TABLE alarm (_id INTEGER PRIMARY KEY, _idplace INTEGER, NAMA TEXT, TIME TEXT)";
		db.execSQL(sql);
		sql = "insert into place (longitude, latitude, nama) values ('112780362','-7298486','kampus')";
		db.execSQL(sql);
	}

	@Override
	public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
		// TODO Auto-generated method stub
		db.execSQL("DROP TABLE IF EXISTS place");
		db.execSQL("DROP TABLE IF EXISTS alarm");
		onCreate(db);
	}
}
